package com.bestv.search.common.dao.hibernate;

import com.bestv.search.common.dao.UserStatusDao;
import com.bestv.search.common.model.UserStatus;
import com.bestv.search.common.model.UserStatusSummary;
import org.hibernate.*;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class UserStatusDaoHibernate extends GenericDaoHibernate<UserStatus, Long> implements UserStatusDao {
    public UserStatusDaoHibernate() {
        super(UserStatus.class);
    }

    @Override
    public void updateUserStatus() {
        getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                SQLQuery sqlQuery = session.createSQLQuery("{call updateUserStatus()}");
                sqlQuery.executeUpdate();
                return null;
            }
        });
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<UserStatusSummary> getUserStatusOfSummary(Date start, Date end) {
        String sql = "select sum(amount) as amount, status, date from (select amount, "
                + "case when status=3 then 2 else status end as status, date from userStatusSummary "
                + "where status <>0 and date>= ? and date<= ?) as t group by status, date order by date, status asc";

        List<UserStatusSummary> result = new ArrayList<UserStatusSummary>();
        Session session = null;
        try {
            session = getSession();
            Query query = session.createSQLQuery(sql).addScalar("amount", Hibernate.INTEGER)
                    .addScalar("status", Hibernate.INTEGER).addScalar("date", Hibernate.DATE)
                    .setResultTransformer(Transformers.aliasToBean(UserStatusSummary.class));
            query.setDate(0, start);
            query.setDate(1, end);

            result = query.list();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }

        return result;
    }

    @Override
    public List<String[]> getNewUserAmountByMonth(String startMonth, String endMonth) {
        List<String[]> result = new ArrayList<String[]>();

        String sql = "select count(*) as amount, DATE_FORMAT(firstAccessDate, '%Y-%m') as month "
                + " from UserStatus where DATE_FORMAT(firstAccessDate, '%Y-%m')>=? "
                + " and DATE_FORMAT(firstAccessDate, '%Y-%m')<= ? group by DATE_FORMAT(firstAccessDate, '%Y-%m') "
                + " order by DATE_FORMAT(firstAccessDate, '%Y-%m') asc";

        ScrollableResults rs = null;
        Session session = null;
        try {
            session = getSession();
            Query query = session.createQuery(sql);
            query.setString(0, startMonth);
            query.setString(1, endMonth);

            rs = query.scroll();
            while (rs.next()) {
                String[] row = new String[2];
                row[0] = rs.getString(1);
                row[1] = String.valueOf(rs.getLong(0));

                result.add(row);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null)
                rs.close();
            if (session != null)
                releaseSession(session);
        }

        return result;
    }
}